﻿#region usings
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using InsuranceAdjuster.Common.Models;
using Papa.Common;
#endregion
namespace InsuranceAdjuster.Web.Services
{
    public class DAL
    {
        public DAL()
        {
        }
        #region Vehicle Info
        public List<int> GetVehicleYears()
        {
            List<int> years = new List<int>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetVehicleYears", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (null != dr)
                    {
                        while (dr.Read())
                        {
                            years.Add(dr.GetInt32(0));
                        }
                    }
                }
            }
            return years;
        }

        public List<string> GetVehicleMakes(int year)
        {
            List<string> makes = new List<string>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetVehicleMakes", conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@year", year));
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (null != dr)
                    {
                        while (dr.Read())
                        {
                            makes.Add(dr.GetString(0));
                        }
                    }
                }
            }
            return makes;
        }


        public List<string> GetVehicleModels(int year, string make)
        {
            if (null == make)
            {
                make = "";
            }
            List<string> models = new List<string>();
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetVehicleModels", conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@year", year));
                    cmd.Parameters.Add(new SqlParameter("@make", make));
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (null != dr)
                    {
                        while (dr.Read())
                        {
                            models.Add(dr.GetString(0));
                        }
                    }
                }
            }
            return models;
        }

        public IQueryable<InsuranceAdjuster.Web.Models.VehicleReferencePhoto> GetVehicleImage(int year, string make, string model)
        {
            Guid PhotoGuid = Guid.Empty;
            using (var MyConnection = new SqlConnection(this.ConnectionString))
            {
                MyConnection.Open();

                var MyCommand = MyConnection.CreateCommand();
                MyCommand.CommandText = "SELECT TOP 1 ReferencePhotoGuid FROM StandardVehicleImage WHERE [Year] LIKE @year AND Make LIKE @Make AND Model LIKE @Model ";
                MyCommand.Parameters.AddWithValue("Year", year.ToString());
                MyCommand.Parameters.AddWithValue("Make", make);
                MyCommand.Parameters.AddWithValue("Model", model);

                var Result = MyCommand.ExecuteScalar();
                if (Result != null)
                    PhotoGuid = (Guid)Result;
            }

            if (PhotoGuid == Guid.Empty)
                return null;

            var MyContext = new SLInsuranceDomainService();
            var PhotoResult = MyContext.GetVehicleReferencePhotoByGuid(PhotoGuid);

            return PhotoResult;
        }
        #endregion
        #region Branch Info
        public BranchStyle GetBranchStyles(string branch_Id)
        {
            InsuranceAdjuster.Common.Models.BranchStyle branchStyle = new Common.Models.BranchStyle();
            string style;
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetBranchStyles", conn))
                {
                    cmd.Parameters.Add(new SqlParameter("@branch_Id", branch_Id));
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (null != dr)
                    {
                        while (dr.Read())
                        {
                            style = dr.GetString(0);
                            switch (style)
                            {
                                case "HeadingBackgroundColor":
                                    branchStyle.HeadingBackgroundColor = dr.GetString(1);
                                    break;
                                case "HeadingForegroundColor":
                                    branchStyle.HeadingForegroundColor = dr.GetString(1);
                                    break;
                                case "BranchFont":
                                    branchStyle.BranchFont = dr.GetString(1);
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
            }
            return branchStyle;
        }
        #endregion

        private string _connectionString;
        private string ConnectionString
        {
            get
            {
                if (null == _connectionString)
                {
                    _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
                }
                return _connectionString;
            }
        }

    }
}